#！/bin/bash
source /etc/profile
# 可以输入日期；如果未输入日期取昨天的时间
if [ -n "$1" ]
then
  do_date=$1
else
  do_date=`date -d "-1 day" +%F`
fi

sql="
with tmp as
(
select count(*) as thirdstart from
(
select uid,dt,tw,sub,ts,(sub+ts) as sum
from
(
select uid,dt,tw,sub,lead(sub) over(partition by uid order by dt) ts
from
(
select uid,dt,tw,
case when date_add(dt,1)=tw then 1
else 0 end sub
from
(
  select uid,dt,lead(dt) over(partition by uid order by dt) tw from dws.dws_member_start_day where dt>=date_add('$do_date',-7) and dt<='$do_date'
) t1
) t2
) t3
) t4 where sum=2
)
insert overwrite table ads.ads_member_active73_count
partition(dt='$do_date')
select thirdstart from tmp;
"

hive -e "$sql"
